package com.sxj.PDMS.utils;

import cn.hutool.core.collection.CollUtil;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.support.ExcelTypeEnum;
import com.sxj.PDMS.common.ErrorCode;
import com.sxj.PDMS.exception.BusinessException;
import com.sxj.PDMS.model.entity.*;
import lombok.extern.slf4j.Slf4j;
import org.springframework.web.multipart.MultipartFile;

import java.io.IOException;
import java.sql.Date;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

/**
 * Excel 相关工具类
 */
@Slf4j
public class ExcelUtils {

    /**
     * excel 转 List<Student>
     *
     * @param multipartFile
     * @return
     */
    public static List<Student> excelToStudentList(MultipartFile multipartFile) {
        List<Map<Integer, String>> list = null;
        try {
            list = EasyExcel.read(multipartFile.getInputStream())
                    .excelType(ExcelTypeEnum.XLSX)
                    .sheet()
                    .headRowNumber(0)
                    .doReadSync();
        } catch (IOException e) {
            log.error("表格处理错误", e);
        }

        if (CollUtil.isEmpty(list)) {
            return new ArrayList<>();
        }

        List<Student> students = new ArrayList<>();
        for (int i = 1; i < list.size(); i++) {  // 从第二行开始，第一行是表头
            LinkedHashMap<Integer, String> dataMap = (LinkedHashMap<Integer, String>) list.get(i);
            Student student = new Student();
            try {
                student.setStudentNumber(dataMap.get(0));
                student.setName(dataMap.get(1));
                student.setIdNumber(dataMap.get(2));
                student.setNation(dataMap.get(3));
                student.setGender(dataMap.get(4));
                student.setPhoneNumber(dataMap.get(5));
                student.setGrade(dataMap.get(6));
                student.setDateOfBirth(parseDate(dataMap.get(7)));
                student.setNativePlace(dataMap.get(8));
                student.setFullHomeAddress(dataMap.get(9));
                student.setThePartyBranch(dataMap.get(10));
                student.setLiveInTheDormitory(dataMap.get(11));
                student.setStatue(parseStatus(dataMap.get(12)));
                student.setEmail(dataMap.get(13));
                student.setTheDateOfSubmissionOfTheApplicationForPartyMembership(parseDate(dataMap.get(14)));
                student.setEducationLevel(Integer.valueOf(dataMap.get(15)));
                student.setEnrollmentTime(parseDate(dataMap.get(16)));
                student.setIsFailingClass(Integer.valueOf(dataMap.get(17)));
                student.setPreviousCredit(Double.valueOf(dataMap.get(18)));
                student.setPosition(dataMap.get(19));
                student.setPassword(dataMap.get(20));
            } catch (ParseException | NumberFormatException e) {
                log.error("数据转换错误", e);
                continue;  // 跳过错误的行
            }
            students.add(student);
        }
        return students;
    }

    /**
     * excel 转 List<ActivistApplicationInformationList>
     *
     * @param multipartFile
     * @return
     */
    public static List<ActivistApplicationInformationList> excelToActivistApplicationList(MultipartFile multipartFile) {
        List<Map<Integer, String>> list = null;
        try {
            list = EasyExcel.read(multipartFile.getInputStream())
                    .excelType(ExcelTypeEnum.XLSX)
                    .sheet()
                    .headRowNumber(0)
                    .doReadSync();
        } catch (IOException e) {
            log.error("表格处理错误", e);
        }

        if (CollUtil.isEmpty(list)) {
            return new ArrayList<>();
        }

        List<ActivistApplicationInformationList> activistApplicationInformationLists = new ArrayList<>();
        for (int i = 1; i < list.size(); i++) {  // 从第二行开始，第一行是表头
            LinkedHashMap<Integer, String> dataMap = (LinkedHashMap<Integer, String>) list.get(i);
            ActivistApplicationInformationList activistApplicationInformationList = new ActivistApplicationInformationList();
            try {
                activistApplicationInformationList.setStudentNumber(dataMap.get(0));
                activistApplicationInformationList.setDateOfApplicationForPartyMembership(parseDate(dataMap.get(1)));
                activistApplicationInformationList.setPromotionTime(parseDate(dataMap.get(2)));
                activistApplicationInformationList.setTimeOfApplication(parseDate(dataMap.get(3)));
                activistApplicationInformationList.setBranchTime(parseDate(dataMap.get(4)));
                activistApplicationInformationList.setTrainPeople(dataMap.get(5));
            } catch (ParseException | NumberFormatException e) {
                log.error("数据转换错误", e);
                continue;  // 跳过错误的行
            }
            activistApplicationInformationLists.add(activistApplicationInformationList);
        }
        return activistApplicationInformationLists;
    }

    /**
     * excel 转 List<DevelopTheObjectApplicationInformationList>
     *
     * @param multipartFile
     * @return
     */
    public static List<DevelopTheObjectApplicationInformationList> excelToDevelopTheObjectApplicationList(MultipartFile multipartFile) {
        List<Map<Integer, String>> list = null;
        try {
            list = EasyExcel.read(multipartFile.getInputStream())
                    .excelType(ExcelTypeEnum.XLSX)
                    .sheet()
                    .headRowNumber(0)
                    .doReadSync();
        } catch (IOException e) {
            log.error("表格处理错误", e);
        }

        if (CollUtil.isEmpty(list)) {
            return new ArrayList<>();
        }

        List<DevelopTheObjectApplicationInformationList> developTheObjectApplicationInformationLists = new ArrayList<>();
        for (int i = 1; i < list.size(); i++) {  // 从第二行开始，第一行是表头
            LinkedHashMap<Integer, String> dataMap = (LinkedHashMap<Integer, String>) list.get(i);
            DevelopTheObjectApplicationInformationList developTheObjectApplicationInformationList = new DevelopTheObjectApplicationInformationList();
            try {
                developTheObjectApplicationInformationList.setStudentNumber(dataMap.get(0));
                developTheObjectApplicationInformationList.setDateOfApplicationForPartyMembership(parseDate(dataMap.get(1)));
                developTheObjectApplicationInformationList.setDetermineActivistTime(parseDate(dataMap.get(2)));
                developTheObjectApplicationInformationList.setBranchTime(parseDate(dataMap.get(4)));
                developTheObjectApplicationInformationList.setTimeOfApplication(parseDate(dataMap.get(3)));
            } catch (ParseException | NumberFormatException e) {
                log.error("数据转换错误", e);
                continue;  // 跳过错误的行
            }
            developTheObjectApplicationInformationLists.add(developTheObjectApplicationInformationList);
        }
        return developTheObjectApplicationInformationLists;
    }


    /**
     * excel 转 List<ActivistApplicationInformationList>
     *
     * @param multipartFile
     * @return
     */
    public static List<ListOfProbationaryPartyApplicationInformation> excelToProbationaryPartyApplicationList(MultipartFile multipartFile) {
        List<Map<Integer, String>> list = null;
        try {
            list = EasyExcel.read(multipartFile.getInputStream())
                    .excelType(ExcelTypeEnum.XLSX)
                    .sheet()
                    .headRowNumber(0)
                    .doReadSync();
        } catch (IOException e) {
            log.error("表格处理错误", e);
        }

        if (CollUtil.isEmpty(list)) {
            return new ArrayList<>();
        }

        List<ListOfProbationaryPartyApplicationInformation> ListOfProbationaryPartyApplicationInformations = new ArrayList<>();
        for (int i = 1; i < list.size(); i++) {  // 从第二行开始，第一行是表头
            LinkedHashMap<Integer, String> dataMap = (LinkedHashMap<Integer, String>) list.get(i);
            ListOfProbationaryPartyApplicationInformation listOfProbationaryPartyApplicationInformation = new ListOfProbationaryPartyApplicationInformation();
            try {
                listOfProbationaryPartyApplicationInformation.setApplicationNumber(Long.valueOf(dataMap.get(0)));
                listOfProbationaryPartyApplicationInformation.setStudentNumber(dataMap.get(1));
                listOfProbationaryPartyApplicationInformation.setName(dataMap.get(2));
                listOfProbationaryPartyApplicationInformation.setApplicationForm(dataMap.get(3));
                listOfProbationaryPartyApplicationInformation.setReasonForApplication(dataMap.get(4));
                listOfProbationaryPartyApplicationInformation.setDateOfApplicationForPartyMembership(parseDate(dataMap.get(5)));
                listOfProbationaryPartyApplicationInformation.setDetermineActivistTime(parseDate(dataMap.get(6)));
                listOfProbationaryPartyApplicationInformation.setDetermineTheDevelopmentObjectTime(parseDate(dataMap.get(7)));
                listOfProbationaryPartyApplicationInformation.setTimeOfApplication(parseDate(dataMap.get(8)));
                listOfProbationaryPartyApplicationInformation.setBranchTime(parseDate(dataMap.get(9)));
                listOfProbationaryPartyApplicationInformation.setAgreeOrNot(dataMap.get(10));
                listOfProbationaryPartyApplicationInformation.setReply(dataMap.get(11));
            } catch (ParseException | NumberFormatException e) {
                log.error("数据转换错误", e);
                continue;  // 跳过错误的行
            }
            ListOfProbationaryPartyApplicationInformations.add(listOfProbationaryPartyApplicationInformation);
        }
        return ListOfProbationaryPartyApplicationInformations;
    }

    public static List<FullPartyMemberInformationList> excelToFullPartyApplicationList(MultipartFile multipartFile) {
        List<Map<Integer, String>> list = null;
        try {
            list = EasyExcel.read(multipartFile.getInputStream())
                    .excelType(ExcelTypeEnum.XLSX)
                    .sheet()
                    .headRowNumber(0)
                    .doReadSync();
        } catch (IOException e) {
            log.error("表格处理错误", e);
        }

        if (CollUtil.isEmpty(list)) {
            return new ArrayList<>();
        }

        List<FullPartyMemberInformationList> ListOfFullPartyApplicationInformations = new ArrayList<>();
        for (int i = 1; i < list.size(); i++) {  // 从第二行开始，第一行是表头
            LinkedHashMap<Integer, String> dataMap = (LinkedHashMap<Integer, String>) list.get(i);
            FullPartyMemberInformationList fullPartyMemberInformationList = new FullPartyMemberInformationList();
            try {
                fullPartyMemberInformationList.setApplicationNumber(Long.valueOf(dataMap.get(0)));
                fullPartyMemberInformationList.setStudentNumber(dataMap.get(1));
                fullPartyMemberInformationList.setName(dataMap.get(2));
                fullPartyMemberInformationList.setApplicationForm(dataMap.get(3));
                fullPartyMemberInformationList.setReasonForApplication(dataMap.get(4));
                fullPartyMemberInformationList.setDateOfApplicationForPartyMembership(parseDate(dataMap.get(5)));
                fullPartyMemberInformationList.setDetermineActivistTime(parseDate(dataMap.get(6)));
                fullPartyMemberInformationList.setDetermineTheDevelopmentObjectTime(parseDate(dataMap.get(7)));
                fullPartyMemberInformationList.setDetermineProbationaryMembershipTime(parseDate(dataMap.get(8)));
                fullPartyMemberInformationList.setTimeOfApplication(parseDate(dataMap.get(9)));
                fullPartyMemberInformationList.setBranchTime(parseDate(dataMap.get(10)));
                fullPartyMemberInformationList.setAgreeOrNot(dataMap.get(11));
                fullPartyMemberInformationList.setReply(dataMap.get(12));
            } catch (ParseException | NumberFormatException e) {
                log.error("数据转换错误", e);
                continue;  // 跳过错误的行
            }
            ListOfFullPartyApplicationInformations.add(fullPartyMemberInformationList);
        }
        return ListOfFullPartyApplicationInformations;
    }


    private static Date parseDate(String dateStr) throws ParseException {
        if (dateStr == null || dateStr.isEmpty()) {
            return null;
        }

        // 尝试解析多种日期格式
        String[] dateFormats = {"yyyy-MM-dd", "yyyy/MM/dd"};
        for (String format : dateFormats) {
            try {
                SimpleDateFormat sdf = new SimpleDateFormat(format);
                sdf.setLenient(false);
                return new Date(sdf.parse(dateStr).getTime());
            } catch (ParseException e) {
            }
        }
        throw new ParseException("Unparseable date: " + dateStr, 0);
    }

    private static Integer parseStatus(String statusStr) {
        switch (statusStr) {
            case "群众":
                return 0;
            case "团员":
            case "共青团员":
                return 1;
            case "已提交入党申请书":
                return 2;
            case "积极分子":
                return 3;
            case "发展对象":
                return 4;
            case "预备党员":
                return 5;
            case "正式党员":
                return 6;
            default:
                throw new NumberFormatException("Invalid status: " + statusStr);
        }
    }
}
